package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.common.WebContext;
import cn.ztydata.teachingsystem.heaven.cons.QuestionCons;
import cn.ztydata.teachingsystem.heaven.cons.Roles;
import cn.ztydata.teachingsystem.heaven.entity.Question;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 问题Dao类
 *
 * @author lyj
 * @since 2015-3-19
 */
@Repository
public class QuestionDao extends BaseDao{
    @Autowired
    private WebContext webContext;


    /**
     * 新增问题
     *
     * @param question 问题实体
     * @return int 受影响行数
     * @author lyj
     * @since 2015-3-19
     */
    public int addQuestion(Question question){

        StringBuilder sql = new StringBuilder("INSERT INTO t_question VALUES (NULL, :sTitle, :sContent, :tiChoiceness, NOW(), :iCreatorId, :tiRole, :iGroupId, :tiStatus)");

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("sTitle", question.getTitle());
        param.addValue("sContent", question.getContent());
        param.addValue("tiChoiceness", QuestionCons.UNCHOICENESS); //0不是精选 1是精选
        param.addValue("iCreatorId", webContext.getUserId());
        param.addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0); //0老师  1学生
        param.addValue("iGroupId", question.getGroupId());
        param.addValue("tiStatus", QuestionCons.NOT_SOLVED); //0待解决  1已解决

        log.debug("新增问题");
        return namedParameterJdbcTemplate.update(sql.toString(), param);

    }


    /**
     * 验证问题是否可编辑
     * @param id 问题ID
     * @return Question 问题实体
     * @author lyj
     * @since 2015-3-19
     */
    public int checkQuestion(final long id){
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_question WHERE id = :id ");
        sql.append(" AND iCreatorId = :iCreatorId AND tiRole = :tiRole AND tiChoiceness = 0 AND tiStatus = 0");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("id", id)
                .addValue("iCreatorId", webContext.getUserId())
                .addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0);

        log.debug("验证问题是否可编辑");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);

    }


    /**
     * 判断问题是否已解决
     *
     * @param id 问题ID
     * @return int
     * @author lyj
     * @since 2015-3-26
     */
    public int checkStatus(final long id){
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_question WHERE id = :id AND tiStatus = 0");

        log.debug("判断问题是否已解决");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("id", id), Integer.class);
    }


    /**
     * 判断问题创建人
     *
     * @param questionId 问题ID
     * @return int
     * @author lyj
     * @since 2015-3-26
     */
    public int checkCreator(final long questionId){
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_question WHERE id = :id AND iCreatorId = :iCreatorId  AND tiRole = :tiRole");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("id", questionId)
                .addValue("iCreatorId", webContext.getUserId())
                .addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0);

        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
    }



    /**
     * 编辑问题
     *
     * @param question 问题实体
     * @return int
     * @author lyj
     * @since 2015-3-19
     */
    public int editQuestion(Question question){
        StringBuilder sql = new StringBuilder("UPDATE t_question SET sTitle = :sTitle, sContent = :sContent, iGroupId = :iGroupId WHERE id = :id");

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("sTitle", question.getTitle());
        param.addValue("sContent", question.getContent());
        param.addValue("iGroupId", question.getGroupId());
        param.addValue("id", question.getId());

        log.debug("编辑问题");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }


    /**
     * 验证问题所在课程群组是否为登录用户所管理课程群组
     *
     * @param questionId 问题ID
     * @return int
     * @author lyj
     * @since 2015-3-25
     */
    public int checkGroup(final long questionId){
        //根据问题ID查询其所在课程群组ID，根据群组ID获取其负责教师工号，如果教师工号和登录教师ID对应
        StringBuilder sql = new StringBuilder("SELECT COUNT(id) FROM t_teacher WHERE id = :teacherId AND sNumber = (SELECT sTeacherNum FROM t_course_group WHERE id = (SELECT iGroupId FROM t_question WHERE id = :questionId)) LIMIT 1");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("questionId", questionId)
                .addValue("teacherId", webContext.getUserId());

        log.debug("验证问题所在课程群组是否为登录用户所管理课程群组");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
    }


    /**
     * 设置精选
     *
     * @param question 问题实体
     * @return int
     * @author lyj
     * @since 2015-3-19
     */
    public int setChoiceness(Question question){
        StringBuilder sql = new StringBuilder("UPDATE t_question SET tiChoiceness = :tiChoiceness WHERE id = :id");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("tiChoiceness", question.getChoiceness())
                .addValue("id", question.getId());

        log.debug("设置精选");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }


    /**
     * 设置已解决
     *
     * @param id 问题ID
     * @param status 状态（0待解决 1已解决）
     * @return int
     * @author lyj
     * @since 2015-3-19
     */
    public int setStatus(final long id, final int status){
        StringBuilder sql = new StringBuilder("UPDATE t_question SET tiStatus = :tiStatus WHERE id = :id");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("tiStatus", status)
                .addValue("id", id);

        log.debug("设置已解决");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }


    /**
     * 删一个问题
     *
     * @param id 问题ID
     * @return
     * @author lyj
     * @since 2015-3-19
     */
    public int deleteQuestion(final long id){

        StringBuilder sql = new StringBuilder("DELETE FROM t_question WHERE id = :id ");

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id", id);

        log.debug("删一个问题");
        return namedParameterJdbcTemplate.update(sql.toString(), param);
    }


    /**
     * 获取问题明细
     *
     * @param id 问题ID
     * @return Question
     * @author lyj
     * @since 2015-3-19
     */
    public Question getDetail(final long id){

        StringBuilder sql = new StringBuilder("SELECT dtCreate, sTitle, sContent, tiChoiceness, tiStatus, iCreatorId, tiRole FROM t_question WHERE id = :id ");

        MapSqlParameterSource param = new MapSqlParameterSource().addValue("id", id);

        try {
            log.debug("获取问题明细");
            return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new RowMapper<Question>() {
                @Override
                public Question mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Question reQuestion = new Question();

                    reQuestion.setCreate(rs.getTimestamp("dtCreate"));
                    reQuestion.setTitle(rs.getString("sTitle"));
                    reQuestion.setContent(rs.getString("sContent"));
                    reQuestion.setChoiceness(rs.getInt("tiChoiceness"));
                    reQuestion.setStatus(rs.getInt("tiStatus"));
                    reQuestion.setCreatorId(rs.getLong("iCreatorId"));
                    reQuestion.setRole(rs.getInt("tiRole"));

                    return reQuestion;
                }
            });
        }catch (EmptyResultDataAccessException ex){

            log.debug("获取问题明细失败");
            log.warn("获取习题明细失败: id->{}", id);

            return null;
        }
    }


    /**
     * 获取问题列表
     *
     * @param groupId 群组ID
     * @param choiceness 是否为精选（0不是  1是）
     * @param status 状态（0待解决 1已解决）
     * @param creatorId 创建人ID
     * @param pageNum 当前页码
     * @param pageSize 每页条数

     * @return Page
     * @author lyj
     * @since 2015-3-20
     */
    public Page<Question> getList(final long groupId, final int choiceness, final int status, final long creatorId, final int pageNum, final int pageSize){
        StringBuilder sql = new StringBuilder("SELECT id, dtCreate, sTitle, tiChoiceness, tiStatus, iCreatorId, tiRole FROM t_question");
        StringBuilder sqlCount = new StringBuilder("SELECT COUNT(*) FROM t_question");

        StringBuilder where = new StringBuilder(" WHERE 1=1");

        MapSqlParameterSource param = new MapSqlParameterSource();

        //过滤群组ID
        if (groupId > 0){
            log.debug("过滤群组IDgroupId > 0");
            where.append(" AND iGroupId = :iGroupId");
            param.addValue("iGroupId", groupId);
        }

        //过滤精选
        if (choiceness == 0 || choiceness == 1){
            log.debug("过滤精选choiceness == 0 || choiceness == 1");
            where.append(" AND tiChoiceness = :tiChoiceness");
            param.addValue("tiChoiceness", choiceness);
        }

        //过滤状态
        if (status == 0 || status == 1){
            log.debug("过滤状态status == 0 || status == 1");
            where.append(" AND tiStatus = :tiStatus");
            param.addValue("tiStatus", status);
        }

        //过滤创建人
        if (creatorId > 0 ){
            log.debug("过滤创建人creatorId > 0");
            where.append(" AND iCreatorId = :iCreatorId AND tiRole = :tiRole");
            param.addValue("iCreatorId", creatorId);
            param.addValue("tiRole", webContext.getRole() == Roles.STUDENT ? 1 : 0);
        }

        //获取总条数
        int total = namedParameterJdbcTemplate.queryForObject(sqlCount.append(where).toString(), param, Integer.class);

        if (total == 0) {
            log.debug("获取总条数为零：total == 0");
            return new Page<>();
        }

        //追加排序

        where.append(" ORDER BY tiChoiceness DESC, dtCreate DESC");

        //追加limit限制
        where.append(getLimitStr(pageNum, pageSize));

        List<Question> questionList = namedParameterJdbcTemplate.query(sql.append(where).toString(), param, new RowMapper<Question>() {
            @Override
            public Question mapRow(ResultSet rs, int rowNum) throws SQLException {
                Question question = new Question();

                question.setId(rs.getLong("id"));
                question.setCreate(rs.getTimestamp("dtCreate"));
                question.setTitle(rs.getString("sTitle"));
                question.setChoiceness(rs.getInt("tiChoiceness"));
                question.setStatus(rs.getInt("tiStatus"));
                question.setCreatorId(rs.getLong("iCreatorId"));
                question.setRole(rs.getInt("tiRole"));

                return question;
            }
        });

        log.debug("获取问题列表");
        return new Page<>(total, pageSize, pageNum, questionList);
    }


    /**
     * 学生默认页获取统计信息
     *
     * @return map
     * @author lyj
     * @since 2015-4-2
     */
    public Map<String, Object> getCount() {
        StringBuilder sql = new StringBuilder("SELECT " +
                "(SELECT COUNT(q.id) FROM t_question q WHERE q.iCreatorId = :creatorId AND q.tiRole = :role) myQuestionCount, " +
                "(SELECT COUNT(n.id) FROM t_note n WHERE n.iCreatorId = :creatorId AND n.tiRole = :role) myNotesCount, " +
                "(SELECT COUNT(stc.id) FROM t_student_course stc WHERE stc.sCourseCode IN (SELECT sc.sCourseCode FROM t_student s JOIN t_student_course sc ON sc.sStudentNum = s.sNumber WHERE s.id = :creatorId)) myCourseClassmatesCount, " +
                "(SELECT COUNT(id) FROM t_student WHERE sClassNum = (SELECT sClassNum FROM t_student WHERE id = 1)) myClassCount," +
                "COUNT(r.id) myResourceCount FROM t_resource r WHERE r.iUploaderId = :creatorId AND r.tiRole = :role");

        MapSqlParameterSource param = new MapSqlParameterSource()
                .addValue("creatorId", webContext.getUserId())
                .addValue("role", 1);

        log.debug("学生默认页获取统计信息");
        return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> map = new HashMap<String, Object>();

                map.put("myQuestionCount", resultSet.getInt("myQuestionCount"));
                map.put("myResourceCount", resultSet.getInt("myResourceCount"));
                map.put("myClassmatesCount", resultSet.getInt("myCourseClassmatesCount") + resultSet.getInt("myClassCount"));
                map.put("myNotesCount", resultSet.getInt("myNotesCount"));

                return map;
            }
        });

    }

}
